# -*_ codeing=utf-8 -*-
# @Time: 2025/10/28 21:36
# @Author: foxhuty
# @File: cascade_query.py
# @Software: PyCharm
# @Based on python 3.10
from sqlalchemy import update, select, extract, func
from sqlalchemy.orm import sessionmaker
from sqlalchemy_notes.db_main import engine
from sqlalchemy_notes.student_manager.models import Student, Department, IdCard

with sessionmaker(bind=engine).begin() as session:
    dept1 = session.get(Department, 2)
    print(dept1.name)
    # 当前部门的所有员工
    for stu in dept1.student_list:
        print(stu)
    # 打印一个员工所在的部门
    stu1 = session.get(Student, 7)
    print(stu1.name)

    # print(stu1.dept.name)
    # 设置一个员工的部门为空/修改部门id
    session.execute(update(Student).where(Student.id == 1).values(dept_id=2))

    stu3 = session.get(Student, 6)
    print(stu3)
    print(stu3.dept_id)
    print(stu3.id_card.card_number)
    # #查找2015年入职的员工.isouter为外连接
    sql_stmt = select(Student.name,Department.name).join(Department,isouter=True).where(extract('year',Student.entry_date)==2018)
    result_query=session.execute(sql_stmt)
    # print(result_query.all())
    for obj in result_query.all():
        print(f'{obj[0]}----{obj[1]}')

    #查身份证号及所在部门
    id_stmt=select(Student.name,Department.name).join(Department,isouter=True).join(IdCard).where(IdCard.card_number=='512535196502252512')
    result_query=session.execute(id_stmt)
    for obj in result_query:
        print(obj.name,obj.name_1)

    #查询每个部门名字，城市及部门下的员工个数
    sql_stmt=select(Department,func.count(Student.id)).join(Student,isouter=True).group_by(Department.id)
    result_query=session.execute(sql_stmt)
    for obj in result_query:
        print(obj[0])